package dao.mysql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.logging.Level;

import model.FacilityDTO;
import model.search.FacilitySearchDTO;

import dao.DAOException;
import dao.FacilityDAO;

public class FacilityDAOImpl extends BaseDAO implements FacilityDAO {
	public FacilityDAOImpl() {
		super();
	}
	
	private ArrayList<FacilityDTO> getList(PreparedStatement ps) throws SQLException {
		ArrayList<FacilityDTO> list = new ArrayList<FacilityDTO>();
		
		FacilityDTO data = null;
		
		ResultSet rs = ps.executeQuery();
		while (rs.next()) {
			data = new FacilityDTO();
			data.setName(rs.getString("Name"));
			data.setType(rs.getString("Type"));
			data.setDescription(rs.getString("Description"));
			data.setSeatingCapacity(rs.getInt("SeatingCapacity"));
			data.setStatus(rs.getInt("Status"));
			list.add(data);
		}
		rs.close();
		
		return list;
	}
	
	public ArrayList<FacilityDTO> getAll() throws DAOException {
		openConnection(false);

		ArrayList<FacilityDTO> list = new ArrayList<FacilityDTO>();
		try {
			String sql = "select * from Facility";
			PreparedStatement prep = this.getPreparedStatement(sql);
			list = getList(prep);
			prep.close();
		} catch (Exception e) {
			String error = "Error on Get All Facilities. Nested Exception is: "
					+ e;
			super.printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
		return list;
	}

	public FacilityDTO getFacility(String name) throws DAOException {
		openConnection(false);

		FacilityDTO facility = null;

		try {
			String sql = "select * from Facility where Name = ? ";
			PreparedStatement preq = getPreparedStatement(sql);
			preq.setString(1, name);
			ResultSet rs = preq.executeQuery();
			if (rs.next()) {
				facility = new FacilityDTO();
				facility.setName(rs.getString("Name"));
				facility.setType(rs.getString("Type"));
				facility.setDescription(rs.getString("Description"));
				facility.setSeatingCapacity(rs.getInt("SeatingCapacity"));
				facility.setStatus(rs.getInt("Status"));
			}

			rs.close();
			preq.close();
		} catch (Exception e) {
			String error = "Error on Get Facility. Nested Exception is: " + e;
			super.printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}

		return facility;
	}

	private String getWhereFilter(FacilitySearchDTO fsd) {
		String where = "";
		
		if (fsd.getName() != null && !fsd.getName().trim().isEmpty()) {
			where = " WHERE ";
			where += " Name LIKE ? ";
		}
		if (fsd.getDescription() != null && !fsd.getDescription().trim().isEmpty()) {
			if (where.isEmpty())
				where = " WHERE ";
			else
				where += " AND ";
			where += " Description LIKE ? ";
		}
		if (fsd.getType() != null && !fsd.getType().trim().isEmpty()) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " Type = ? ";
		}
		if (fsd.getStatus() != null) {
			if (where.isEmpty()) where = " WHERE "; else where += " AND ";
			where += " Status = ? ";
		}
		
		return where;
	}
	
	private ArrayList getParameters(FacilitySearchDTO fsd) {
		ArrayList params = new ArrayList();
		
		if (fsd.getName() != null && !fsd.getName().trim().isEmpty()) {
			params.add("%" + fsd.getName() + "%");
		}
		if (fsd.getDescription() != null && !fsd.getDescription().trim().isEmpty()) {
			params.add("%" + fsd.getDescription() + "%");
		}
		if (fsd.getType() != null && !fsd.getType().trim().isEmpty()) {
			params.add(fsd.getType());
		}
		if (fsd.getStatus() != null) {
			params.add(fsd.getStatus());
		}
		
		return params;
	}
	
	public ArrayList<FacilityDTO> findFacilityByCriteria(FacilitySearchDTO fsd)
			throws DAOException {
		openConnection();

		ArrayList<FacilityDTO> list = new ArrayList<FacilityDTO>();
		try {
			String sql = "";
			String where = "";
			ArrayList params = new ArrayList();

			sql = "SELECT * FROM Facility ";
			where = getWhereFilter(fsd);
			params = getParameters(fsd);

			PreparedStatement prep = getPreparedStatement(sql + " " + where);
			for (int i = 1; i <= params.size(); i++) {
				prep.setObject(i, params.get(i - 1));
			}

			printLog("Executing FindFacilityByCriteria: " + sql + where, Level.INFO);
			
			list = getList(prep);
			
			prep.close();
		} catch (Exception e) {
			String error = "Error FindFacilityByCriteria. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}

		return list;
	}
	
	public void insertFacility(FacilityDTO data) throws DAOException {
		openConnection(false);

		try {
			String sql = "Insert Into Facility ( Name, Description, Type, SeatingCapacity, Status ) " +
		                 "  Values (?, ?, ?, ?, ?) ";
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, data.getName());
			prep.setString(2, data.getDescription());
			prep.setString(3, data.getType());
			prep.setInt(4, data.getSeatingCapacity());
			prep.setInt(5, data.getStatus());
			prep.executeUpdate();

			printLog("Executing Insert: " + sql, Level.INFO);

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Inserting Facility. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}

	public void updateFacility(FacilityDTO facility) throws DAOException {
		openConnection(false);

		try {
			String sql = "Update Facility " + "   Set Description= ? "
					+ " , Type= ? " + " , SeatingCapacity= ? "
					+ " , Status= ? " + " Where Name = ? ";
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, facility.getDescription());
			prep.setString(2, facility.getType());
			prep.setInt(3, facility.getSeatingCapacity());
			prep.setInt(4, facility.getStatus());
			prep.setString(5, facility.getName());

			prep.executeUpdate();

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Updating Facility. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}

	public void deleteFacility(FacilityDTO facility) throws DAOException {
		openConnection(false);
		
		try {
			String sql = "DELETE FROM Facility where Name= ? ";
			PreparedStatement prep = getPreparedStatement(sql);
			prep.setString(1, facility.getName());

			prep.executeUpdate();

			commit();
			prep.close();
		} catch (Exception e) {
			String error = "Error Deleting Facility. Nested Exception is: " + e;
			printLog(error, Level.SEVERE);
			throw new DAOException(error);
		} finally {
			closeConnection();
		}
	}
	
	

	
}
